VBA Function: Split

Description

The VBA Split function is used to split a string of characters according to a delimiter to produce an array of values.

Split syntax

Split(text, separator)

Or

Split(text, separator, limit)

VBA Split example

Separating the site name "www.moonexcel.com.ua" with the delimiter "." to get 4 substrings in the array:

Sub SplitExample1()
    
      text = "www.moonexcel.com.ua"
    
      array = Split(text, ".")
    
      MsgBox array(0) 'Returns: www
      MsgBox array(1) 'Returns: moonexcel
      MsgBox array(2) 'Returns: com
      MsgBox array(3) 'Returns: ua
    
End Sub

If necessary, you can limit the number of elements in the array:

Sub SplitExample2()
    
      text = "www.moonexcel.com.ua"
    
      array = Split(text, ".", 2)
    
      MsgBox array(0) 'Returns: www
      MsgBox array(1) 'Returns: moonexcel.com.ua
    
End Sub

If you only need to retrieve a single substring (in this example, the middle substring "moonexcel"), you can directly specify an array element to retrieve it:

Sub SplitExample3()
    
      text = "www.moonexcel.com.ua"
    
      middle = Split(text, ".")(1)
    
      MsgBox middle 'Returns: moonexcel
    
End Sub